package DAO;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;

import JDBC.DATABASE;
import VO.GroundBook;

public class GroundBookDao {
	
	public ArrayList queryAllGroundBook(){	
		ArrayList groundbook = new ArrayList();
		Connection conn=DATABASE.getConnection();//连接数据库
		String sql="select * from GROUND_BOOK order by bid";
		//sql语句，按照bid排序查看所有预约信息
		ResultSet rs = DATABASE.query(sql);
		try {
			while(rs.next()){
				GroundBook gbook = new GroundBook();
				gbook.setBid(rs.getInt("BID"));
				gbook.setGname(rs.getString("GNAME"));
				gbook.setAccount(rs.getString("ACCOUNT"));
				gbook.setDay(rs.getString("DAY"));
				gbook.setTime(rs.getString("TIME"));
				gbook.setBstatus(rs.getString("BSTATUS"));
				groundbook.add(gbook);				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block	
		}catch (Exception ex) {
			ex.printStackTrace();
		}finally{
			DATABASE.close(conn);//关闭数据库的连接
		}
		return groundbook;
	}
	
	public ArrayList checkAllGroundBook(){	
		ArrayList groundbook = new ArrayList();
		String sql="select * from GROUND_BOOK ";
		ResultSet rs = DATABASE.query(sql);
		try {
			while(rs.next()){
				GroundBook gbook = new GroundBook();
				gbook.setBid(rs.getInt("BID"));
				gbook.setGname(rs.getString("GNAME"));
				gbook.setAccount(rs.getString("ACCOUNT"));
				gbook.setDay(rs.getString("DAY"));
				gbook.setTime(rs.getString("TIME"));
				gbook.setBstatus(rs.getString("BSTATUS"));
				groundbook.add(gbook);				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}catch (Exception ex) {
			
		}	return groundbook;
	}

	public void saveGroundBook(GroundBook groundbook){
		Connection conn=DATABASE.getConnection();
		String sql=" INSERT INTO GROUND_BOOK(BID,GNAME,ACCOUNT,DAY,TIME,BSTATUS) VALUES(ground_book_sequence.Nextval,?,?,?,?,?)";
		try{
			PreparedStatement ps=conn.prepareStatement(sql);
		//	ps.setInt(1,groundbook.getBid());
			ps.setString(1,groundbook.getGname());
			ps.setString(2,groundbook.getAccount());
			ps.setString(3,groundbook.getDay());
			ps.setString(4,groundbook.getTime());
			ps.setString(5,groundbook.getBstatus());
			ps.executeUpdate();
			ps.close();
		//	System.out.println(2);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DATABASE.close(conn);
		}		
	}
	public void updateStatus(int id,String state){
		Connection conn=DATABASE.getConnection();
		String sql="update GROUND_BOOK set BSTATUS='"+state+"' where bid='"+id+"'";
		try{
			PreparedStatement ps=conn.prepareStatement(sql);
		//	ps.setString(1,state);
		//	ps.setInt(2,id);
			ps.executeUpdate();
			ps.close();
		//	System.out.println(2);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DATABASE.close(conn);
		}		
	}
	
	
	public ArrayList queryMyBook(String account){	
		
		ArrayList groundbook = new ArrayList();
		String sql="select * from GROUND_BOOK where ACCOUNT='"+account+"'";
		System.out.println("-----" + sql);
		ResultSet rs = DATABASE.query(sql);	
		try {
			while(rs.next()){
				GroundBook gbook = new GroundBook();
				gbook.setBid(rs.getInt("BID"));
				gbook.setGname(rs.getString("GNAME"));
				
				gbook.setDay(rs.getString("DAY"));
				gbook.setTime(rs.getString("TIME"));
				gbook.setBstatus(rs.getString("BSTATUS"));
				
				System.out.println("-----gbook: " + gbook);
				
				groundbook.add(gbook);	
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}catch (Exception ex) {
			
		}	return groundbook;
	}
	public int user_book(String account,String state)throws SQLException{
		
		Connection conn=DATABASE.getConnection();
		CallableStatement cs=conn.prepareCall("{call book_num(?,?,?)}"); 
		cs.setString(1,account);
		cs.setString(2,state);
		cs.registerOutParameter(3,Types.INTEGER); 
		cs.execute();
		cs.getInt(3);
		System.out.print(cs.getInt(3));
		return cs.getInt(3);
	}

}
